package com.xiaoy.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.PropertyValue;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.PropertyFilter;
import org.springside.modules.orm.PropertyFilter.MatchType;
import org.springside.modules.orm.hibernate.SimpleHibernateDao;
import org.springside.modules.utils.reflection.ConvertUtils;

@Repository("daoUtil")
@Transactional
/**
 * 解决一些不需要实体的sql的查询
 *   eg: 
 *   	1，为一张或多张表插入记录
 *      2，查询多张表
 *      3，报表的一些查询
 * @author chentianyi
 * 2014-3-18 下午4:41:15
 *
 */
public class DaoUtil extends SimpleHibernateDao {

	
	public List<Map<String, Object>> query(String sql, Object... params) {

		SQLQuery query = getSession().createSQLQuery(sql);
		for (int i = 0; i < params.length; i++) {
			Object obj = params[i];
			query.setParameter(i, obj);
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	public List<Map<String, Object>> query(String sql, Map<String,Object>params) {
		SQLQuery query = getSession().createSQLQuery(sql);
		for (Map.Entry<String, Object>e:params.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	public List<Map<String,Object>> execute (String tablename,List<PropertyFilter>params) {
		
		String sql = "select * from $tablename $where ";
		
		String where = " where 1=1 ";
		Object[] ps = new Object[params.size()];
		int i = 0;
		for (PropertyFilter pf : params) {
			where += " and " + buildWhereFor(pf.getPropertyName(),pf.getPropertyValue(), pf.getMatchType());
			ps[i++] = pf.getPropertyValue();
		}
		sql = sql.replace("$tablename", tablename).replace("$where", where);
		return query(sql, ps);
	}
	
	
	public Map<String, Object> queryForMap(String sql, Object... params) {
		List<Map<String, Object>> list = query(sql, params);
		if (list.size() > 0) {
			return list.get(0);
		}
		return new HashMap<String, Object>();
	}
	public Map<String, Object> queryForMap(String sql, Map<String,Object>params) {
		List<Map<String, Object>> list = query(sql, params);
		if (list.size() > 0) {
			return list.get(0);
		}
		return new HashMap<String, Object>();
	}
	
	public<T> List<T> query(Class<T> c, String sql,Object...params) {
		SQLQuery query = getSession().createSQLQuery(sql);
		for (int i = 0; i < params.length; i++) {
			Object obj = params[i];
			query.setParameter(i, obj);
		}
		query.addEntity(c);
		return query.list();
	}
	
	public<T> List<T> query(Class<T> c, String sql,Map<String,Object>params) {
		SQLQuery query = getSession().createSQLQuery(sql);
		for (Map.Entry<String, Object>e:params.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
		query.addEntity(c);
		return query.list();
	}
	
	public<T> Page<T> query (Page<T>page,String sql ,Object...params) {
		
		SQLQuery query = getSession().createSQLQuery(sql);
		for (int i = 0; i < params.length; i++) {
			Object obj = params[i];
			query.setParameter(i, obj);
		}
		page.setResult(query.setFirstResult(page.getPageNo()).setMaxResults(page.getPageSize()).list());;
		return page;
	}
	
	public<T> Page<T> query (Page<T>page,String sql ,Map<String,Object>params) {
		
		SQLQuery query = getSession().createSQLQuery(sql);
		for (Map.Entry<String, Object>e:params.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
		page.setResult(query.setFirstResult(page.getPageNo()).setMaxResults(page.getPageSize()).list());;
		return page;
	}
	/**
	 * 
	 * @param c 返回的bean
	 * @param sql sql数据
	 * @param params sql语句总的？
	 * @return
	 */
	public<T> T queryForBean (Class<T> c, String sql,Object...params) {
		List<T> list = query(c, sql, params);
		if (list.size()>0) {
			return list.get(0);
		}
		return null;
	}
	
	// 更新操作 start ---------------------------
	
	// 参数为?  id=? and name like ?
	public void saveOrUpdate(Object t) {
		getSession().saveOrUpdate(t);
	}
	
	public int saveOrUpdate(String sql, Object... params) {
		SQLQuery query = getSession().createSQLQuery(sql);
		for (int i = 0; i < params.length; i++) {
			Object obj = params[i];
			query.setParameter(i, obj);
		}
		return query.executeUpdate();
	}
	
	public int saveOrUpdate(String sql, List<Object> params) {
		return saveOrUpdate(sql, params.toArray());
	}
	
	// 参数为#  id=#id and name like #name
	public int saveOrUpdate(String sql,Map<String,Object>params) {
		SQLQuery query = getSession().createSQLQuery(sql);
		for (Map.Entry<String, Object>e:params.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
		return query.executeUpdate();
	}
	
	public int save (String tablename,Map<String,Object>params) {
		String sql = "insert into "+tablename+" ($fields) values ($values)";
		
		String fields = "";
		String values = "";
		for (Map.Entry<String, Object> e: params.entrySet()) {
			fields = e.getKey()+",";
			values = "#"+e.getKey()+",";
		}
		fields.substring(0, fields.length()-1);
		sql = sql.replace("$fields", fields).replace("$values", values);
		return saveOrUpdate(sql,params);
	}
	
//	public int update (String tablename,Map<String,Object>params) {
//		String sql = " insert into tablename ";
//		
//		return 1;
//	}
	
	// 更新操作 end ---------------------------
	
	// -------------------------

	// where name=‘#name’
	protected String buildWhereFor(final String propertyName, final Object propertyValue, final MatchType matchType) {
		Assert.hasText(propertyName, "propertyName不能为空");
		Criterion criterion = null;
		
		if (propertyValue instanceof Date) {
			
		}
		String where = propertyName ;
		// 根据MatchType构造criterion
		switch (matchType) {
			
			case EQ:
//				criterion = Restrictions.eq(propertyName, propertyValue);
				where += " = ? ";
				break;
			case NE:
//				criterion = Restrictions.ne(propertyName, propertyValue);
				where += " <> ? ";
				break;
			case LIKE:
				if (propertyValue instanceof String) {
					where += "like '%'||?||'%' ";
				} else {
					where += "= ? ";
				}
				break;
			case LE:
				where += " <= ? ";
				break;
			case LT:
				where += " < ? ";
				break;
			case GE:
				where += " >= ? ";
				break;
			case GT:
				where += " > ? ";
				break;
			case IN:
				where += "is null";
				break;
			case NN:
				where += "is not null";
				break;
			case LKR:
				where += " like ? || '%'";
//			default :
//				where += " 1=1 ";
//				break;
		}
		return where;
	}

	
//	private String parsParam(List<PropertyFilter> pfs) {
//
//		String where = " where 1=1 ";
//
//		for (PropertyFilter pf : pfs) {
//			where += " and " + buildWhereFor(pf.getPropertyName(),pf.getPropertyValue(), pf.getMatchType());
//		}
//		return where;
//	}
	
	
	
	public static void main(String[] args) {
		String value = "2014_02_03";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//		System.out.println(sdf.format("2014-02-02"));
		;
//		Date d = new Date(20140202);
//		System.out.println(d);
//		System.out.println(sdf.format(d));
		
		Object o = ConvertUtils.convertStringToObject(value, Date.class);
		
		System.out.println(o);
		System.out.println(sdf.format(o));
	}
	
}